from openpyxl import load_workbook # 操作 xlsx
from openpyxl.styles import Alignment # 设置 xlsx 格式
import random
import datetime
# 官网地址 https://pypi.org/project/chinesecalendar/
# 每年需要更新 pip install -U chinesecalendar
import chinese_calendar
# import pandas as pd

# python3.11

# 随机生成总额为amount的发票列表(保证总和等于amount)
def invoice_list_with_amount(amount):
    if amount <= 150: 
        return [amount]
    if amount <= 280:
        first_num = random.randint(1, 10) + int(amount / 2)
        return [first_num, amount - first_num]
    num = random.randint(1, 15)
    average = 135
    return [average + num] + invoice_list_with_amount(amount - average - num)

# 随机生成总额为amount的发票列表(保证每张大于指定数)
def invoice_average_list_with_amount(amount):
    if amount <= 0: 
        return []
    num = random.randint(1, 15)
    average = 135
    return [average + num] + invoice_average_list_with_amount(amount - average - num)


# 开始时间和结束时间
def invoice_time_with(num):
    time_22 = datetime.datetime.strptime( # 字符串生成时间
        '22:00', '%H:%M'
    ) # 22 点开始加班
    start_minutes = random.randint(3, 18) # 添加随机 3-18分钟作为开始时间
    start_time = time_22 + datetime.timedelta(minutes=start_minutes)
    minutes_total = int(num / 200 * 60) # 按一小时200推算时间
    end_time = start_time + datetime.timedelta(minutes=minutes_total)
    start_str = start_time.strftime( # 时间格式化成字符串
        '%H:%M'
    )
    end_str = end_time.strftime(
        '%H:%M'
    )
    return (start_str + '-' + end_str)

def write_excel(amount, startT, endT):
    # 生成发票金额列表
    amount_list = invoice_average_list_with_amount(amount)
    print('生成发票列表成功')
    print(amount_list)
    amount = 0
    for n in amount_list:
        amount += n
    print('发票 - ', len(amount_list), "张发票，总额为", amount)

    # 删除旧数据
    file='./file/打车票.xlsx'
    wb=load_workbook(filename=file) # 获取工作簿对象
    ws=wb.active # 工作表对象
    ws.delete_rows(2, 100)

    # 获取指定月份的工作日时间
    start_time = startT
    end_time = endT
    date_lists = chinese_calendar.get_workdays(start_time, end_time)
    # for date in date_lists:
    #     print('工作日 - ', date)

    # 写入新数据
    for i in range(0, len(amount_list)):
        num = amount_list[i]
        date = date_lists[i]
        invoice_time_range = invoice_time_with(num)
        ws.append([i + 1, date, num, invoice_time_range])
    # 设置格式居中
    align = Alignment(horizontal='center', vertical='center', wrapText=True)
    for i in range(1, ws.max_row + 1):
        for j in range(1, ws.max_column + 1):
            ws.cell(i, j).alignment = align
    # 保存
    wb.save(file)

if __name__ == "__main__":
    total_amount = 4322
    start_date = datetime.date(
        2024, 10, 14
    )
    end_date = datetime.date(
        2024, 12, 30
    )
    write_excel(total_amount, start_date, end_date)
    print('执行完成~')